﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using GestorDeFlotas.Core;

namespace GestorDeFlotasDesktop.MejoresAutos
{
    public partial class MejoresAutos : Form
    {
        public MejoresAutos(int anio, int mesInicio, int mesFin)
        {
            InitializeComponent();
            this.Anio = anio;
            this.MesInicio = mesInicio;
            this.MesFin = mesFin;
        }

        private int Anio { get; set; }

        private int MesInicio { get; set; }

        private int MesFin { get; set; }
        
        private BindingSource bindingSource1 = new BindingSource();
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();

        private void MejoresAutos_Load(object sender, EventArgs e)
        {
            dataGridView1.DataSource = bindingSource1;
            GetData(@"SELECT TOP 5 A.auto_patente AS Patente, A.auto_licencia AS Licencia, SUM(T.tur_valor_bandera + (V.via_cant_fichas * T.tur_valor_ficha)) AS Importe
                        FROM CRAZY_TAXI.TL_VIAJES AS V
	                        INNER JOIN CRAZY_TAXI.TL_ChoferesPorAutoYTurno AS C ON V.viacho_id = C.catcho_id 
		                        AND V.viatur_id = C.cattur_id 
		                        AND YEAR(V.via_fecha) = YEAR(C.cat_fecha)
                                AND MONTH(V.via_fecha) = MONTH(C.cat_fecha)
                                AND DAY(V.via_fecha) = DAY(C.cat_fecha)
	                        INNER JOIN CRAZY_TAXI.TL_TURNOS AS T ON T.tur_id = V.viatur_id
	                        INNER JOIN CRAZY_TAXI.TL_Autos AS A ON C.catauto_codigo = A.auto_codigo
                        WHERE YEAR(V.via_fecha) = " + Anio + " AND MONTH(V.via_fecha) BETWEEN " + MesInicio + " AND " + MesFin +
                        @" GROUP BY A.auto_patente, A.auto_licencia
                        ORDER BY SUM(T.tur_valor_bandera + (V.via_cant_fichas * T.tur_valor_ficha)) DESC");
        }//AND CAST(CONVERT(VARCHAR(10), V.via_fecha, 111) AS DATETIME) = C.cat_fecha

        private void btnCerrar_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void GetData(string selectCommand)
        {
            try
            {
                using (SqlConnection connection = SqlUtils.GetConnection())
                {
                    connection.Open();
                    // Create a new data adapter based on the specified query.
                    dataAdapter = new SqlDataAdapter(selectCommand, connection.ConnectionString);

                    // Populate a new data table and bind it to the BindingSource.
                    DataTable table = new DataTable();
                    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                    dataAdapter.Fill(table);
                    bindingSource1.DataSource = table;

                    // Resize the DataGridView columns to fit the newly loaded content.
                    dataGridView1.AutoResizeColumns();
                }
            }
            catch (SqlException)
            {
                MessageBox.Show("Error al intentar obtener los datos de la base.");
            }
        }
    }
}
